﻿
CREATE PROCEDURE [dbo].[balcon_GenNotaContabilaUnion]
	@cont TipCont,
	@CodUnitate smallint,
	@DeLaData datetime,
	@PanaLaData datetime,
	@An smallint,
	@strFiltruCont varchar(2000),
	@strFiltruNrNota varchar(2000),
	@strFiltruJurnal varchar(2000)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	if @An <= 0
	 BEGIN
		SELECT b.NumarDoc, b.DataDoc, b.NumarNota, a.ContDebit, a.ContCredit, 
						  b.IDJurnal, a.ValoareLei, a.DataDocJust, a.TipDocJust, a.NumarDocJust, a.ExplicatiiDocJust, 
						  b.TipFormula, c.Categorie, 'D' AS Tip, a.CodUnitate
					FROM  [Rulaje] a INNER JOIN  [RulajeDoc] b ON a.IDDocument = b.IDDocument 
					INNER JOIN  [Conturi] c ON a.ContDebit = c.Cont AND c.CodUnitate = a.CodUnitate
					WHERE [ContDebit]<>'N/A' AND (a.CodUnitate = @CodUnitate) AND
					(@cont = '' OR (a.ContDebit like  @cont + '%' OR a.ContCredit like @cont + '%'))
					AND (b.DataDoc >=@DeLaData AND b.DataDoc<= @PanaLaData)
					AND (LEN(LTRIM(RTRIM(@strFiltruNrNota)))=0 OR (b.NumarNota NOT IN (SELECT fart.value FROM dbo.SplitParameters(@strFiltruNrNota,',') fart)))
					AND (LEN(LTRIM(RTRIM(@strFiltruCont)))=0 OR (ContDebit IN (SELECT fart.value FROM dbo.SplitParameters(@strFiltruCont,',') fart)))
					AND (LEN(LTRIM(RTRIM(@strFiltruJurnal)))=0 OR (b.IDJurnal IN (SELECT fart.value FROM dbo.SplitParameters(@strFiltruJurnal,',') fart)))
		UNION 
		SELECT b.NumarDoc, b.DataDoc, b.NumarNota, a.[ContCredit] AS [ContDebit], a.[ContDebit] AS ContCredit, 
						  b.IDJurnal, a.ValoareLei, a.DataDocJust, a.TipDocJust, a.NumarDocJust, a.ExplicatiiDocJust, 
						  b.TipFormula, c.Categorie, 'C' AS Tip, a.CodUnitate
					FROM  [Rulaje] a INNER JOIN  [RulajeDoc] b ON a.IDDocument = b.IDDocument 
					INNER JOIN  [Conturi] c ON a.ContDebit = c.Cont AND c.CodUnitate = a.CodUnitate
					WHERE [ContCredit]<>'N/A' AND (a.CodUnitate = @CodUnitate) 
					AND (@cont= '' OR (a.ContDebit like @cont + '%' OR a.ContCredit like @cont + '%'))
					AND (b.DataDoc >=@DeLaData AND b.DataDoc<= @PanaLaData)
					AND (LEN(LTRIM(RTRIM(@strFiltruNrNota)))=0 OR (b.NumarNota NOT IN (SELECT fart.value FROM dbo.SplitParameters(@strFiltruNrNota,',') fart)))	
					AND (LEN(LTRIM(RTRIM(@strFiltruCont)))=0 OR (ContCredit IN (SELECT fart.value FROM dbo.SplitParameters(@strFiltruCont,',') fart)))
					AND (LEN(LTRIM(RTRIM(@strFiltruJurnal)))=0 OR (b.IDJurnal IN (SELECT fart.value FROM dbo.SplitParameters(@strFiltruJurnal,',') fart)))
		ORDER BY b.DataDoc
	END
	ELSE
	BEGIN
		SELECT b.NumarDoc, b.DataDoc, b.NumarNota, a.ContDebit, a.ContCredit, 
						  b.IDJurnal, a.ValoareLei, a.DataDocJust, a.TipDocJust, a.NumarDocJust, a.ExplicatiiDocJust, 
						  b.TipFormula, c.Categorie, 'D' AS Tip, a.CodUnitate
					FROM  [arhRulaje] a INNER JOIN  [arhRulajeDoc] b ON a.IDDocument = b.IDDocument 
					INNER JOIN  [arhConturi] c ON a.ContDebit = c.Cont AND c.CodUnitate = a.CodUnitate AND a.An=c.An
					WHERE [ContDebit]<>'N/A' AND (a.CodUnitate = @CodUnitate) AND
					a.An = @An AND
					(@cont = '' OR (a.ContDebit like  @cont + '%' OR a.ContCredit like @cont + '%'))
					AND (b.DataDoc >=@DeLaData AND b.DataDoc<= @PanaLaData)
					AND (LEN(LTRIM(RTRIM(@strFiltruNrNota)))=0 OR (b.NumarNota NOT IN (SELECT fart.value FROM dbo.SplitParameters(@strFiltruNrNota,',') fart)))
					AND (LEN(LTRIM(RTRIM(@strFiltruCont)))=0 OR (ContDebit IN (SELECT fart.value FROM dbo.SplitParameters(@strFiltruCont,',') fart)))
					AND (LEN(LTRIM(RTRIM(@strFiltruJurnal)))=0 OR (b.IDJurnal IN (SELECT fart.value FROM dbo.SplitParameters(@strFiltruJurnal,',') fart)))
		UNION 
		SELECT b.NumarDoc, b.DataDoc, b.NumarNota, a.[ContCredit] AS [ContDebit], a.[ContDebit] AS ContCredit, 
						  b.IDJurnal, a.ValoareLei, a.DataDocJust, a.TipDocJust, a.NumarDocJust, a.ExplicatiiDocJust, 
						  b.TipFormula, c.Categorie, 'C' AS Tip, a.CodUnitate
					FROM  [arhRulaje] a INNER JOIN  [arhRulajeDoc] b ON a.IDDocument = b.IDDocument 
					INNER JOIN  [arhConturi] c ON a.ContDebit = c.Cont AND c.CodUnitate = a.CodUnitate  AND a.An=c.An
					WHERE [ContCredit]<>'N/A' AND (a.CodUnitate = @CodUnitate) and
					a.An = @An 
					AND (@cont= '' OR (a.ContDebit like @cont + '%' OR a.ContCredit like @cont + '%'))
					AND (b.DataDoc >=@DeLaData AND b.DataDoc<= @PanaLaData)
					AND (LEN(LTRIM(RTRIM(@strFiltruNrNota)))=0 OR (b.NumarNota NOT IN (SELECT fart.value FROM dbo.SplitParameters(@strFiltruNrNota,',') fart)))	
					AND (LEN(LTRIM(RTRIM(@strFiltruCont)))=0 OR (ContCredit IN (SELECT fart.value FROM dbo.SplitParameters(@strFiltruCont,',') fart)))
					AND (LEN(LTRIM(RTRIM(@strFiltruJurnal)))=0 OR (b.IDJurnal IN (SELECT fart.value FROM dbo.SplitParameters(@strFiltruJurnal,',') fart)))
		ORDER BY b.DataDoc
	
	END

END